﻿

CREATE proc [CashSale].[GetCashSale]
@inCashSaleID	int = null
as
begin
	
	select	cs.CashSaleID,
			cs.InvoiceNo,
			cs.OrgKey,
			o.[Name] 'OrgName',
			cs.SaleDate,
			dt.SalePrice,
			dt.Tax,
			dt.SalePrice + dt.Tax 'GrossPrice',
			cs.UserID,
			u.LoginName,
			cs.PaymentMode,
			cs.OrderDate,
			cs.OrderDetail,
			da.Name		'DeliverName',
			da.Name2	'DeliverName2',
			da.Ort		'DeliverOrt',
			da.Plz		'DeliverPlz',
			da.Strasse	'DeliverStrasse',
			o.[Name] + ' ' + case when coalesce(o.Prefix,'') != '' then o.Prefix + ' ' else '' end
			+ case when coalesce(rf.RechtsFBez,'') != '' then rf.RechtsFBez + ' ' else '' end
			+ coalesce(o.ZusätzeFW,'') 
			+ '(' + coalesce(plz.Plz,'') + ' ' + coalesce(plz.Ort,'') 
			+ ', ' + coalesce(o.Straße,'')
			+ ')' 'OrgFullName',
			cs.UseDeliveryAddress,
			cs.WriteToLiquiDate,
			cs.WriteInvoiceToBvDate,
			cs.WriteInvoiceToVrspDate
	from	CashSale.datCashSale cs
			left join dbo.Organisation o on
				o.OrgKey = cs.OrgKey
			left join dbo.Rechtsform rf on
				rf.RechtsFKey = o.RechtsFKey
			left join dbo.Postleitzahl plz on
				o.EPLZ = plz.EPLZ
			left join (
				select	d.CashSaleID,
						sum(d.SalePrice) 'SalePrice',
						sum(d.SalePrice * d.TaxRate) 'Tax'
				from	CashSale.datCashSaleDetails d
				group by d.CashSaleID
				) dt on
					dt.CashSaleID = cs.CashSaleID
			inner join [User].datUser u on
				u.UserID = cs.UserID
			left join CashSale.datDeliveryAddresses da on
				da.CashSaleID = cs.CashSaleID
	where	(cs.CashSaleID = @inCashSaleID
				or coalesce(@inCashSaleID,0) = 0)
	order by cs.CashSaleID desc
end


GO


